-- 退转件汇总
-- 侯文龙
-- 日期：2022-08-05
insert overwrite table jms_dm.dm_reback_transfer_waybill_type_sum_dt partition(dt)
select
             max(end_manage_region_code)as manage_code      --管理大区code
             ,max(end_manage_region_name) as manage_name     --管理大区名字
             ,max(end_agent_code) as agent_code              --代理区code
             ,max(end_agent_name) as agent_name              --代理区名字
             ,max(end_franchisee_code) as franchisee_code                       --加盟商code
             ,max(end_franchisee_name) as franchisee_name                       --加盟商名字
             ,final_sign_network_code as delivery_network_code        --派件网点code
             , max(final_sign_network_name) as delivery_network_name   --派件网点名字
             ,order_source_name                                      --平台名字
             ,max(order_source_code) as order_source_code           --平台code
             ,count(1) as back_sign_cnt                             --应签收量
             ,sum(is_aging_sign_22) as before_sign_cnt               --22点前签收量
             ,sum(after_aging_sign_22) as after_sign_cnt  --22点后延误签收
             ,sum(is_aging_sign_24) as zypt_cnt    --22点到24点签收量
             ,sum(if(aging_sign_time is null,1,0)) as no_sign_cnt --未签收量
             ,sum(if(deliver_shift=1,1,0)) as ypy_cnt --一班应签收量
             ,sum(if(deliver_shift=1 and aging_sign_time<=final_plan_sign_time ,1,0))as ypz_cnt --一班准点签收的量
             ,sum(if(deliver_shift=2,1,0)) as epy_cnt  --二班应签收的量
             ,sum(if(deliver_shift=2 and aging_sign_time<=final_plan_sign_time ,1,0))as epz_cnt   --二班准点签收的量
             ,sum(if(deliver_shift=3,1,0)) as spy_cnt  --三班应签收的量
             ,sum(if(deliver_shift=3 and aging_sign_time<=final_plan_sign_time ,1,0))as spz_cnt   -- 三班准点签收的量
             ,type1 as business_type     --类型 1 发件 2 签收
             ,dt as date_time       --业务日期
             ,sum(if(deadline_signing_time= '10:30:00',1,0 )) as need_sign_cnt_1030                                                            -- 10：30应签收
             ,sum(if(deadline_signing_time= '10:30:00' and aging_sign_time<= final_plan_sign_time,1,0 )) as aging_sign_cnt_1030                -- 10：30准点签收
             ,sum(if(deadline_signing_time= '11:30:00',1,0 )) as need_sign_cnt_1130                                                            -- 11：30应签收
             ,sum(if(deadline_signing_time= '11:30:00' and aging_sign_time <= final_plan_sign_time,1,0 )) as aging_sign_cnt_1130               -- 11：30准点签收
             ,sum(if(deadline_signing_time= '14:00:00',1,0 )) as need_sign_cnt_1400                                                            -- 14：00应签收
             ,sum(if(deadline_signing_time= '14:00:00' and aging_sign_time <= final_plan_sign_time,1,0 )) as aging_sign_cnt_1400               -- 14：00准点签收
             ,sum(if(deadline_signing_time= '18:00:00',1,0 )) as need_sign_cnt_1800                                                            -- 18：00应签收
             ,sum(if(deadline_signing_time= '18:00:00' and aging_sign_time <= final_plan_sign_time,1,0 )) as aging_sign_cnt_1800               -- 18：00准点签收
             ,sum(if(deadline_signing_time= '22:00:00',1,0)) as need_sign_cnt_2200                                                              -- 22:00应签收量
             ,sum(if(deadline_signing_time= '22:00:00' and aging_sign_time <= final_plan_sign_time ,1,0)) as aging_sign_cnt_2200                -- 22:00准点签收量
     ,sum(if(sign_type=3,1,0)) as no_appraisal_sum
             ,dt                       --分区
        from jms_dm.dm_reback_transfer_waybill_type_detail_dt
        where dt > date_add('{{ execution_date | cst_ds }}',-30)
        and dt<='{{ execution_date | cst_ds }}'
       --  and type1 = 1
        group by dt,type1,final_sign_network_code,order_source_name
         distribute by dt, abs(hash(delivery_network_code)) % 1 ;